{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The autoreload extension is already loaded. To reload it, use:\n",
      "  %reload_ext autoreload\n"
     ]
    }
   ],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>order_no</th>\n",
       "      <th>user_id</th>\n",
       "      <th>vip_id</th>\n",
       "      <th>course_id</th>\n",
       "      <th>article_id</th>\n",
       "      <th>status</th>\n",
       "      <th>channel</th>\n",
       "      <th>real_amount</th>\n",
       "      <th>from</th>\n",
       "      <th>payway</th>\n",
       "      <th>create_time</th>\n",
       "      <th>version_code</th>\n",
       "      <th>in_type</th>\n",
       "      <th>buy_type</th>\n",
       "      <th>create_time_format</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>348219</td>\n",
       "      <td>scsq0520240522000413022062839390</td>\n",
       "      <td>2206283</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>成功</td>\n",
       "      <td>default</td>\n",
       "      <td>68.0</td>\n",
       "      <td>IOS</td>\n",
       "      <td>苹果支付</td>\n",
       "      <td>1716307453</td>\n",
       "      <td>155</td>\n",
       "      <td>1027</td>\n",
       "      <td>购买VIP</td>\n",
       "      <td>2024-05-21 16:04:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>348220</td>\n",
       "      <td>scsq0420240522000701022064531574</td>\n",
       "      <td>2206453</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>待处理</td>\n",
       "      <td>vivo</td>\n",
       "      <td>188.0</td>\n",
       "      <td>安卓</td>\n",
       "      <td>支付宝</td>\n",
       "      <td>1716307621</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>购买VIP</td>\n",
       "      <td>2024-05-21 16:07:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>348221</td>\n",
       "      <td>scsq0520240522000710021574218574</td>\n",
       "      <td>2157421</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>待处理</td>\n",
       "      <td>default</td>\n",
       "      <td>8.0</td>\n",
       "      <td>IOS</td>\n",
       "      <td>苹果支付</td>\n",
       "      <td>1716307630</td>\n",
       "      <td>155</td>\n",
       "      <td>1014</td>\n",
       "      <td>购买积分</td>\n",
       "      <td>2024-05-21 16:07:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>348222</td>\n",
       "      <td>scsq0520240522000733021574215852</td>\n",
       "      <td>2157421</td>\n",
       "      <td>90</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>成功</td>\n",
       "      <td>default</td>\n",
       "      <td>8.0</td>\n",
       "      <td>IOS</td>\n",
       "      <td>苹果支付</td>\n",
       "      <td>1716307653</td>\n",
       "      <td>155</td>\n",
       "      <td>1014</td>\n",
       "      <td>购买积分</td>\n",
       "      <td>2024-05-21 16:07:33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>348223</td>\n",
       "      <td>scsq0420240522000758022024663898</td>\n",
       "      <td>2202466</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>成功</td>\n",
       "      <td>xiaomi</td>\n",
       "      <td>98.0</td>\n",
       "      <td>安卓</td>\n",
       "      <td>支付宝</td>\n",
       "      <td>1716307678</td>\n",
       "      <td>31</td>\n",
       "      <td>1018</td>\n",
       "      <td>购买VIP</td>\n",
       "      <td>2024-05-21 16:07:58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>716</th>\n",
       "      <td>348935</td>\n",
       "      <td>scsq0320240522234811021992694899</td>\n",
       "      <td>2199269</td>\n",
       "      <td>95</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>成功</td>\n",
       "      <td>rongyao</td>\n",
       "      <td>8.0</td>\n",
       "      <td>安卓</td>\n",
       "      <td>微信支付</td>\n",
       "      <td>1716392891</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>购买积分</td>\n",
       "      <td>2024-05-22 15:48:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>717</th>\n",
       "      <td>348936</td>\n",
       "      <td>scsq0420240522235213022104898108</td>\n",
       "      <td>2210489</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>待处理</td>\n",
       "      <td>oppo</td>\n",
       "      <td>188.0</td>\n",
       "      <td>安卓</td>\n",
       "      <td>支付宝</td>\n",
       "      <td>1716393133</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>购买VIP</td>\n",
       "      <td>2024-05-22 15:52:13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>718</th>\n",
       "      <td>348937</td>\n",
       "      <td>scsq0420240522235416022104771596</td>\n",
       "      <td>2210477</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>成功</td>\n",
       "      <td>huawei</td>\n",
       "      <td>188.0</td>\n",
       "      <td>安卓</td>\n",
       "      <td>支付宝</td>\n",
       "      <td>1716393256</td>\n",
       "      <td>30</td>\n",
       "      <td>1034</td>\n",
       "      <td>购买VIP</td>\n",
       "      <td>2024-05-22 15:54:16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>719</th>\n",
       "      <td>348938</td>\n",
       "      <td>scsq0320240522235641022105031271</td>\n",
       "      <td>2210503</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>待处理</td>\n",
       "      <td>oppo</td>\n",
       "      <td>188.0</td>\n",
       "      <td>安卓</td>\n",
       "      <td>微信支付</td>\n",
       "      <td>1716393401</td>\n",
       "      <td>31</td>\n",
       "      <td>1008</td>\n",
       "      <td>购买VIP</td>\n",
       "      <td>2024-05-22 15:56:41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>720</th>\n",
       "      <td>348939</td>\n",
       "      <td>scsq0420240522235821018658112513</td>\n",
       "      <td>1865811</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>待处理</td>\n",
       "      <td>vivo</td>\n",
       "      <td>188.0</td>\n",
       "      <td>安卓</td>\n",
       "      <td>支付宝</td>\n",
       "      <td>1716393501</td>\n",
       "      <td>31</td>\n",
       "      <td>1027</td>\n",
       "      <td>购买VIP</td>\n",
       "      <td>2024-05-22 15:58:21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>721 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                          order_no  user_id  vip_id  course_id  \\\n",
       "0    348219  scsq0520240522000413022062839390  2206283      24          0   \n",
       "1    348220  scsq0420240522000701022064531574  2206453     100          0   \n",
       "2    348221  scsq0520240522000710021574218574  2157421      90          0   \n",
       "3    348222  scsq0520240522000733021574215852  2157421      90          0   \n",
       "4    348223  scsq0420240522000758022024663898  2202466       5          0   \n",
       "..      ...                               ...      ...     ...        ...   \n",
       "716  348935  scsq0320240522234811021992694899  2199269      95          0   \n",
       "717  348936  scsq0420240522235213022104898108  2210489     100          0   \n",
       "718  348937  scsq0420240522235416022104771596  2210477     100          0   \n",
       "719  348938  scsq0320240522235641022105031271  2210503     100          0   \n",
       "720  348939  scsq0420240522235821018658112513  1865811     100          0   \n",
       "\n",
       "     article_id status  channel  real_amount from payway  create_time  \\\n",
       "0             0     成功  default         68.0  IOS   苹果支付   1716307453   \n",
       "1             0    待处理     vivo        188.0   安卓    支付宝   1716307621   \n",
       "2             0    待处理  default          8.0  IOS   苹果支付   1716307630   \n",
       "3             0     成功  default          8.0  IOS   苹果支付   1716307653   \n",
       "4             0     成功   xiaomi         98.0   安卓    支付宝   1716307678   \n",
       "..          ...    ...      ...          ...  ...    ...          ...   \n",
       "716           0     成功  rongyao          8.0   安卓   微信支付   1716392891   \n",
       "717           0    待处理     oppo        188.0   安卓    支付宝   1716393133   \n",
       "718           0     成功   huawei        188.0   安卓    支付宝   1716393256   \n",
       "719           0    待处理     oppo        188.0   安卓   微信支付   1716393401   \n",
       "720           0    待处理     vivo        188.0   安卓    支付宝   1716393501   \n",
       "\n",
       "     version_code  in_type buy_type  create_time_format  \n",
       "0             155     1027    购买VIP 2024-05-21 16:04:13  \n",
       "1              31        0    购买VIP 2024-05-21 16:07:01  \n",
       "2             155     1014     购买积分 2024-05-21 16:07:10  \n",
       "3             155     1014     购买积分 2024-05-21 16:07:33  \n",
       "4              31     1018    购买VIP 2024-05-21 16:07:58  \n",
       "..            ...      ...      ...                 ...  \n",
       "716            31        0     购买积分 2024-05-22 15:48:11  \n",
       "717            31        0    购买VIP 2024-05-22 15:52:13  \n",
       "718            30     1034    购买VIP 2024-05-22 15:54:16  \n",
       "719            31     1008    购买VIP 2024-05-22 15:56:41  \n",
       "720            31     1027    购买VIP 2024-05-22 15:58:21  \n",
       "\n",
       "[721 rows x 16 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "from pandas import DataFrame\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "def fetch_data(url):\n",
    "    response = requests.get(url)\n",
    "    return response.json()\n",
    "\n",
    "\n",
    "def create_dataframe(data):\n",
    "    df = DataFrame(data)\n",
    "    df[\"payway\"] = df[\"payway\"].astype(int)\n",
    "    return df\n",
    "\n",
    "\n",
    "def replace_values(df, replacements):\n",
    "    for column, replace_dict in replacements.items():\n",
    "        if column in df.columns:\n",
    "            df[column] = df[column].replace(replace_dict)\n",
    "    return df\n",
    "\n",
    "\n",
    "def get_one_day_orders(day_str):\n",
    "    # 配置信息\n",
    "    url_orders = f\"https://scapi.tayunapi.com/funcLog/orderList?pwd=JAT9voGxoxj7&start={day_str}&end={day_str}\"\n",
    "    # 获取并处理订单数据\n",
    "    orders_data = fetch_data(url_orders)\n",
    "    df_orders = create_dataframe(orders_data[\"list\"])\n",
    "    replacements = {\n",
    "        \"from\": {3: \"安卓\", 4: \"IOS\"},\n",
    "        \"payway\": {\n",
    "            3: \"微信支付\",\n",
    "            4: \"支付宝\",\n",
    "            5: \"苹果支付\",\n",
    "        },\n",
    "        \"buy_type\": {11: \"购买VIP\", 22: \"购买素材包\", 33: \"购买文章\", 44: \"购买积分\"},\n",
    "        \"status\": {1: \"待处理\", 2: \"成功\", 3: \"失败\", 4: \"退款\", 5: \"部分退款\"},\n",
    "    }\n",
    "    df_orders = replace_values(df_orders, replacements)\n",
    "    # create_time 字段是 1716134402 这种时间戳格式，需要转换为正常时间格式\n",
    "    df_orders[\"real_amount\"] = df_orders[\"real_amount\"].astype(float)\n",
    "    df_orders[\"create_time_format\"] = pd.to_datetime(df_orders[\"create_time\"], unit=\"s\")\n",
    "    return df_orders\n",
    "\n",
    "\n",
    "df_orders = get_one_day_orders(\"2024-05-22\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 获取充值入口数据并合并\n",
    "# 匹配订单的类型\n",
    "url_in_type = \"https://scapi.tayunapi.com/funcLog/conf?type=1\"\n",
    "in_type_data = fetch_data(url_in_type)\n",
    "df_in_type = DataFrame(in_type_data)\n",
    "df_in_type.rename(columns={\"id\": \"in_type_id\"}, inplace=True)\n",
    "df_orders = df_orders.merge(\n",
    "    df_in_type[[\"in_type_id\", \"key\", \"title\"]],\n",
    "    left_on=\"in_type\",\n",
    "    right_on=\"in_type_id\",\n",
    "    how=\"left\",\n",
    ")\n",
    "# 删除 in_type_id 列\n",
    "df_orders.drop(columns=\"in_type_id\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "from db_tools import MysqlTool\n",
    "with MysqlTool() as db_tool:\n",
    "    db_tool.upsert_df(df_orders, \"order_table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2024-05-01', '2024-05-02', '2024-05-03', '2024-05-04', '2024-05-05']\n",
      "['2024-05-20', '2024-05-21', '2024-05-22', '2024-05-23', '2024-05-24', '2024-05-25', '2024-05-26', '2024-05-27', '2024-05-28', '2024-05-29', '2024-05-30']\n"
     ]
    }
   ],
   "source": [
    "from tools import base_tools\n",
    "\n",
    "\n",
    "# 示例用法\n",
    "start_date = \"2024-05-01\"\n",
    "end_date = \"2024-05-5\"\n",
    "print(base_tools.generate_date_range(start_date, end_date))\n",
    "\n",
    "base_date = \"2024-05-20\"\n",
    "offset = 10\n",
    "print(base_tools.generate_dates_from_offset(base_date, offset))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "myenv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
